package whf.framework.tools.db;

import java.sql.*;

/**
 * @author wanghaifeng
 *
 */
public class ModifyTable {
	private static void modifyTables() throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost/information_schema?useServerPrepStmts=false&useUnicode=true&characterEncoding=UTF-8", "root","");
		Statement stmt1 = con1.createStatement();
		Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost/framework?useServerPrepStmts=false&useUnicode=true&characterEncoding=UTF-8", "root","");
		Statement stmt2 = con2.createStatement();
		ResultSet rs = stmt1.executeQuery("select * from tables where table_schema='framework' and table_collation='latin1_swedish_ci'");
		while(rs.next()){
			String table_name = rs.getString("table_name");
			String sql = "ALTER TABLE "+table_name+" CHARACTER SET utf8 COLLATE utf8_general_ci;";
			stmt2.execute(sql);
			System.out.println(sql);
		}
		con1.close();
		con2.close();
	}
	private static void modifyColumns() throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost/information_schema?useServerPrepStmts=false&useUnicode=true&characterEncoding=UTF-8", "root","");
		Statement stmt1 = con1.createStatement();
		Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost/framework?useServerPrepStmts=false&useUnicode=true&characterEncoding=UTF-8", "root","");
		Statement stmt2 = con2.createStatement();
		ResultSet rs = stmt1.executeQuery("select table_name, column_name, column_type,column_comment from information_schema.columns where character_set_name='latin1' and table_schema='framework'");
		while(rs.next()){
			String table_name = rs.getString(1);
			String column_name = rs.getString(2);
			String column_type = rs.getString(3);
			String column_comment = rs.getString(4);
			String sql = "ALTER TABLE "+table_name+" MODIFY COLUMN "+column_name+" "+column_type+" CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '"+column_comment+"' ;";
			stmt2.execute(sql);
			System.out.println(sql);
		}
		con1.close();
		con2.close();
	}
	public static void main(String[] args) throws Exception{
		modifyTables();
		modifyColumns();
		System.out.println("Finish!");
	}
}
